package org.jeecg.modules.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Update;

public interface TaskCalculatorMapper extends BaseMapper {

    @Update("SET sql_safe_updates = 0")
    void setSqlSafeUpdates();

    @Update("truncate table biz_meter_data_tmp")
    void truncateBizMeterDataTmp();

    @Insert("insert into biz_meter_data_tmp select a.*,row_number() over (order by measure_time asc) as no from biz_meter_data a " +
            "where device_id=#{eqId} and measure_time >= DATE_SUB(NOW(), INTERVAL ${interval} MONTH)")
    void insertBizMeterDataTmpByMeterData(@Param("eqId") String eqId, @Param("interval") String interval);

    @Delete("delete from biz_meter_data_use where device_id=#{eqId} and measure_time >= DATE_SUB(NOW(), INTERVAL ${interval} MONTH)")
    void deleteBizMeterDataUse(@Param("eqId") String eqId,@Param("interval") String interval);

    /****
     * 1、储能耗电用反向；其他耗电用正向
     * 2、用电户计量设备不计算发电，直接为0；其他计算发电用正向
     * @param eqId
     * @param eqType 判断计算规则用：储能子站计量设备-3；用电户计量设备-4
     */
    @Insert(" insert into biz_meter_data_use" +
            " SELECT * from ( " +
            "      SELECT a.device_id, " +
            "          b.measure_time, " +
            "          b.negative_active_energy - a.negative_active_energy power_consumption, " +
            "          if(${eqType} = 2 or ${eqType} = 3 or ${eqType} = 5,b.positive_active_energy - a.positive_active_energy , 0) power_generation, " +
            "          now() create_time, " +
            "          b.apparent_power  " +
            "          FROM  " +
            "              biz_meter_data_tmp a left join biz_meter_data_tmp b on a.device_id=b.device_id and b.no = a.no+1 where a.device_id=#{eqId}) tmp " +
            " where measure_time is not null ")
    void insertBizMeterDataUse(@Param("eqId") String eqId,@Param("eqType") String eqType);


    @Delete("delete from biz_meter_data_hour a where a.device_id=#{eqId} and exists (select 1 FROM biz_meter_data_use b where a.device_id = b.device_id and a.measure_time = DATE_FORMAT(b.measure_time, '%Y-%m-%d %H:00:00'))")
    void deleteMeterDataHour(@Param("eqId") String eqId);

    @Insert("insert into biz_meter_data_hour(device_id,measure_time,power_consumption,power_generation,create_time) " +
            "   (SELECT" +
            "    device_id," +
            "    DATE_FORMAT(measure_time, '%Y-%m-%d %H:00:00') measure_time, " +
            "    sum(power_consumption) power_consumption, " +
            "    sum(power_generation) power_generation, " +
            "    now() create_time " +
            "   FROM " +
            "    biz_meter_data_use where device_id=#{eqId} " +
            "   GROUP BY device_id,DATE_FORMAT(measure_time, '%Y-%m-%d %H:00:00') order by device_id,DATE_FORMAT(measure_time, '%Y-%m-%d %H:00:00'))")
    void insertMeterDataHour(@Param("eqId") String eqId,@Param("eqType") String eqType);


    @Delete("delete from biz_meter_data_day a where exists (select 1 FROM " +
            "    biz_meter_data_hour b where a.device_id = b.device_id and DATE_FORMAT(a.measure_time, '%Y-%m-%d') = DATE_FORMAT(b.measure_time, '%Y-%m-%d') and b.measure_time >= DATE_SUB(NOW(), INTERVAL ${interval} DAY) )")
    void deleteMeterDataDay(@Param("interval") String interval);


    @Insert("insert into biz_meter_data_day " +
            "SELECT " +
            "    device_id, " +
            "    DATE_FORMAT(measure_time, '%Y-%m-%d 00:00:00') measure_time, " +
            "    sum(power_consumption) power_consumption, " +
            "    sum(power_generation) power_generation, " +
            "    now() create_time " +
            "FROM " +
            "    biz_meter_data_hour where measure_time >= DATE_SUB(NOW(), INTERVAL ${interval} DAY) " +
            "GROUP BY device_id,DATE_FORMAT(measure_time, '%Y-%m-%d 00:00:00') order by device_id,DATE_FORMAT(measure_time, '%Y-%m-%d 00:00:00')")
    void insertMeterDataDay(@Param("interval") String interval);


    @Delete("delete from biz_meter_data_month a where exists (select 1 FROM " +
            "    biz_meter_data_day b where a.device_id = b.device_id and DATE_FORMAT(a.measure_time, '%Y-%m') = DATE_FORMAT(b.measure_time, '%Y-%m') and b.measure_time >= DATE_SUB(NOW(), INTERVAL ${interval} MONTH) )")
    void deleteMeterDataMonth(@Param("interval") String interval);

    @Insert("insert into biz_meter_data_month " +
            "SELECT " +
            "    device_id, " +
            "    DATE_FORMAT(measure_time, '%Y-%m-01 00:00:00') measure_time, " +
            "    sum(power_consumption) power_consumption, " +
            "    sum(power_generation) power_generation, " +
            "    now() create_time " +
            "FROM " +
            "    biz_meter_data_day where measure_time >= DATE_SUB(NOW(), INTERVAL ${interval} MONTH) " +
            "GROUP BY device_id,DATE_FORMAT(measure_time, '%Y-%m-01 00:00:00') order by device_id,DATE_FORMAT(measure_time, '%Y-%m-01 00:00:00')")
    void insertMeterDataMonth(@Param("interval") String interval);


    @Delete("delete from biz_meter_data_year a where exists (select 1 FROM " +
            "    biz_meter_data_month b where a.device_id = b.device_id and DATE_FORMAT(a.measure_time, '%Y') = DATE_FORMAT(b.measure_time, '%Y') and b.measure_time >= DATE_SUB(NOW(), INTERVAL ${interval} YEAR) )")
    void deleteMeterDataYear(@Param("interval") String interval);

    @Insert("insert into biz_meter_data_year " +
            "SELECT " +
            "    device_id, " +
            "    DATE_FORMAT(measure_time, '%Y-01-01 00:00:00') measure_time, " +
            "    sum(power_consumption) power_consumption, " +
            "    sum(power_generation) power_generation, " +
            "    now() create_time " +
            "FROM " +
            "    biz_meter_data_month where measure_time >= DATE_SUB(NOW(), INTERVAL ${interval} YEAR) " +
            "GROUP BY device_id,DATE_FORMAT(measure_time, '%Y-01-01 00:00:00') order by device_id,DATE_FORMAT(measure_time, '%Y-01-01 00:00:00')")
    void insertMeterDataYear(@Param("interval") String interval);
}
